package com.hst.capacity.infra.persistence.jpa.setting.line;

import com.hst.capacity.domain.model.entity.setting.line.IntercityLineEntity;
import com.hst.capacity.domain.model.response.setting.line.CustomCityLineVO;
import com.hst.capacity.domain.model.response.setting.line.IntercityLineVO;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface IntercityLineRepository extends CrudRepository<IntercityLineEntity, String> {

    /**
     * 通过起始城市搜索城市线路
     * @param cityCode
     * @return
     */
    @Query("select b " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on b.idCityLine = a.idCityLine " +
            "where a.status = 1 " +
            "and a.cityCode = :cityCode " +
            "and b.originFlag = 0 " +
            "order by a.sort desc, a.createTime asc")
    List<IntercityLineEntity> findByStartCityCode(@Param("cityCode")String cityCode);

    /**
     * 通过终点城市搜索城市线路
     * @param cityCode
     * @return
     */
    @Query("select b " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on b.idCityLine = a.idCityLine " +
            "where a.status = 1 " +
            "and a.cityCode = :cityCode " +
            "and b.originFlag = 1 " +
            "order by a.sort desc, a.createTime asc")
    List<IntercityLineEntity> findByEndCityCode(@Param("cityCode")String cityCode);

    /**
     * 通过运营商ID上线状态查询
     * @param idFranchisee
     * @return
     */
    List<IntercityLineEntity> findByIdFranchiseeAndOnlineStatus(String idFranchisee,Integer onlineStatus);

    @Query("select t from IntercityLineEntity t where t.idCityLine = ?1 order by t.originFlag")
    List<IntercityLineEntity> findByIdCityLine(String idCityLine);
    List<IntercityLineEntity> findByIdFranchiseeAndIdCityLineIsNotNull(String idFranchisee);

    /**
     * 通过线路ID查找线路信息
     * @param idLine
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.setting.line.IntercityLineVO(cl,icLine) " +
            "from CityLineEntity cl " +
            "inner join IntercityLineEntity icLine on cl.idCityLine = icLine.idCityLine " +
            "where cl.status = 1 " +
            "and icLine.idLine = :idLine " +
            "order by cl.sort desc")
    IntercityLineVO findByIdLine(String idLine);

    @Query("select t from IntercityLineEntity t where t.idLine = ?1")
    IntercityLineEntity findIntercityLineEntityByIdLine(String idLine);

    IntercityLineEntity findByStartCityCodeAndEndCityCode(@Param("startCityCode")String startCityCode,@Param("endCityCode")String endCityCode);
    /**
     * 通过站点地区搜索城市线路
     * @param stationRegionCode
     * @return
     */
    @Query("select b " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on b.idCityLine = a.idCityLine " +
            "where a.status = 1 " +
            "and a.stationRegionCode = :stationRegionCode " +
            "order by a.sort desc, a.createTime asc")
    List<IntercityLineEntity> findByStationRegionCode(@Param("stationRegionCode")String stationRegionCode);

    /**
     * 通过起始城市搜索城市线路
     * @param cityCode
     * @return
     */
    @Query("select b " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on b.idCityLine = a.idCityLine " +
            "where a.status = 1 " +
            "and a.cityCode = :cityCode " +
            "and b.originFlag = 0 " +
            "and b.idCityLine = :idCityLine " +
            "order by a.sort desc, a.createTime asc")
    List<IntercityLineEntity> findByIdCityLineAndStartCityCode(@Param("cityCode")String cityCode,@Param("idCityLine")String idCityLine);

    /**
     * 通过终点城市搜索城市线路
     * @param cityCode
     * @return
     */
    @Query("select b " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on b.idCityLine = a.idCityLine " +
            "where a.status = 1 " +
            "and a.cityCode = :cityCode " +
            "and b.originFlag = 1 " +
            "and b.idCityLine = :idCityLine " +
            "order by a.sort desc, a.createTime asc")
    List<IntercityLineEntity> findByIdCityLineAndEndCityCode(@Param("cityCode")String cityCode,@Param("idCityLine")String idCityLine);

    /**
     * 通过线路ID查找线路信息
     * @param cityCode
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.setting.line.CustomCityLineVO(a,b) " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on a.idCityLine = b.idCityLine " +
            "where a.status = 1 " +
            "and a.cityCode = :cityCode " +
            "and b.originFlag = 0 " +
            "and b.idCityLine = :idCityLine " +
            "order by a.sort desc, a.createTime asc")
    List<CustomCityLineVO>  findVoByIdCityLineAndStartCityCode(@Param("cityCode")String cityCode,@Param("idCityLine")String idCityLine);

    /**
     * 通过线路ID查找线路信息
     * @param cityCode
     * @return
     */
    @Query("select new com.hst.capacity.domain.model.response.setting.line.CustomCityLineVO(a,b) " +
            "from CityLineEntity a " +
            "inner join IntercityLineEntity b on a.idCityLine = b.idCityLine " +
            "where a.status = 1 " +
            "and a.cityCode = :cityCode " +
            "and b.originFlag = 1 " +
            "and b.idCityLine = :idCityLine " +
            "order by a.sort desc, a.createTime asc")
    List<CustomCityLineVO>  findVoByIdCityLineAndEndCityCode(@Param("cityCode")String cityCode,@Param("idCityLine")String idCityLine);

}
